﻿using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using NPOI.HPSF;
using NPOI.POIFS.FileSystem;
using NPOI.SS.Util;

namespace NPOI.Test
{
    class Program
    {
        static void Main(string[] args)
        {
            //创建工作薄
            HSSFWorkbook wk = new HSSFWorkbook();
            //创建一个名称为桔子科技项目预算会签表的表     
            ISheet sheet = wk.CreateSheet("桔子科技项目预算会签表");
            IRow row = sheet.CreateRow(0);
            ICell cell = row.CreateCell(0);
            cell.SetCellValue("桔子科技项目预算会签表");
            //sheet.SetColumnWidth(0, 30 * 256);
            //sheet.SetColumnWidth(1, 30 * 256);
            ICellStyle style = wk.CreateCellStyle();
            style.Alignment = HorizontalAlignment.Center;//居中 
            IFont font = wk.CreateFont();
            font.FontHeight = 20 * 20;//采用 20 号字体         
            style.SetFont(font);
            cell.CellStyle = style;
            // 单元格合并
            sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, 16));// 把 A1:K1 单元格合并
            sheet.AddMergedRegion(new CellRangeAddress(1, 2, 0, 1));// 把 A2:B3 单元格合并 项目名称
            sheet.AddMergedRegion(new CellRangeAddress(1, 2, 2, 4));// 把 A2:B3 单元格合并 xxxxx
            IRow row1 = sheet.CreateRow(1);
            ICell cell10 = row1.CreateCell(0);
            ICell cell12 = row1.CreateCell(2);
            cell10.SetCellValue("项目名称");
            cell12.SetCellValue("xxxxx");

            sheet.AddMergedRegion(new CellRangeAddress(1, 1, 5, 6));//项目起始日期
            sheet.AddMergedRegion(new CellRangeAddress(1, 1, 7, 8));//2016/2/12
            ICell cell15 = row1.CreateCell(5);
            ICell cell17 = row1.CreateCell(7);
            cell15.SetCellValue("项目起始日期");
            cell17.SetCellValue("2016/2/12"); 
         
            sheet.AddMergedRegion(new CellRangeAddress(1, 1, 9, 10));//预计完成日期
            sheet.AddMergedRegion(new CellRangeAddress(1, 1, 11, 12));//2016/5/12
            ICell cell19 = row1.CreateCell(9);
            ICell cell111 = row1.CreateCell(11);
            cell19.SetCellValue("预计完成日期");
            cell111.SetCellValue("2016/5/12"); 
            sheet.AddMergedRegion(new CellRangeAddress(1, 1, 13, 14));//预计回款日期
            sheet.AddMergedRegion(new CellRangeAddress(1, 1, 15, 16));//2016/6/12
            ICell cell113 = row1.CreateCell(13);
            ICell cell115 = row1.CreateCell(15);
            cell113.SetCellValue("预计回款日期");
            cell115.SetCellValue("2016/5/12"); 
            sheet.AddMergedRegion(new CellRangeAddress(3, 3, 0, 1));//项目类别
            sheet.AddMergedRegion(new CellRangeAddress(3, 3, 2, 4));//xxxxx
            IRow row3 = sheet.CreateRow(3);
            ICell cell30 = row3.CreateCell(0);
            ICell cell34 = row3.CreateCell(2);
            cell30.SetCellValue("项目类别");
            cell34.SetCellValue("xxxxx");
            sheet.AddMergedRegion(new CellRangeAddress(2, 3, 5, 6));//合作方付款单位
            sheet.AddMergedRegion(new CellRangeAddress(2, 3, 7, 8));//   xxxxx     
            IRow row2 = sheet.CreateRow(2);
            ICell cell35= row2.CreateCell(5);
            ICell cell37 = row2.CreateCell(7);
            cell35.SetCellValue("合作方付款单位");
            cell37.SetCellValue("xxxxx");
            sheet.AddMergedRegion(new CellRangeAddress(2, 2, 9, 10));//合作方部门
            sheet.AddMergedRegion(new CellRangeAddress(2, 2, 11, 12));//xxxxx
           
            ICell cell29 = row2.CreateCell(9);
            ICell cell211 = row2.CreateCell(11);
            cell29.SetCellValue("合作方部门");
            cell211.SetCellValue("xxxxx");
            sheet.AddMergedRegion(new CellRangeAddress(2, 2, 13, 14));//合作负责人
            sheet.AddMergedRegion(new CellRangeAddress(2, 2, 15, 16));//xxxxx
            ICell cell213= row2.CreateCell(13);
            ICell cell215 = row2.CreateCell(15);
            cell213.SetCellValue("合作负责人");
            cell215.SetCellValue("xxxxx");
            sheet.AddMergedRegion(new CellRangeAddress(3, 3, 9, 10));//项目经办人
            sheet.AddMergedRegion(new CellRangeAddress(3, 3, 11, 12));//xxxxx
            ICell cell39 = row3.CreateCell(9);
            ICell cell311 = row3.CreateCell(11);
            cell39.SetCellValue("项目经办人");
            cell311.SetCellValue("xxxxx");
            sheet.AddMergedRegion(new CellRangeAddress(3, 3, 13, 14));//催款负责人
            sheet.AddMergedRegion(new CellRangeAddress(3, 3, 15, 16));//xxxxx
            ICell cell313 = row3.CreateCell(13);
            ICell cell315 = row3.CreateCell(15);
            cell313.SetCellValue("催款负责人");
            cell315.SetCellValue("xxxxx");
            sheet.AddMergedRegion(new CellRangeAddress(4, 4, 0, 7));//决算收入
            sheet.AddMergedRegion(new CellRangeAddress(4, 4, 8, 16));//决算成本
            IRow row4 = sheet.CreateRow(4);
            ICell cell40 = row4.CreateCell(0);
            ICell cell48 = row4.CreateCell(8);
            cell40.SetCellValue("决算收入");
            cell48.SetCellValue("决算成本");
            ICellStyle style1 = wk.CreateCellStyle();
            style1.Alignment = HorizontalAlignment.Center;//居中 
            IFont font1 = wk.CreateFont();
            font1.FontHeight = 15 * 20;//采用 字体         
            style1.SetFont(font1);
            style1.BottomBorderColor = NPOI.HSSF.Util.HSSFColor.Red.Index;
            cell40.CellStyle = style1;
            cell48.CellStyle = style1;
            IRow row5 = sheet.CreateRow(5);
            row5.CreateCell(0).SetCellValue("序号");
            row5.CreateCell(1).SetCellValue("费用名称");
            row5.CreateCell(2).SetCellValue("规格");
            row5.CreateCell(3).SetCellValue("单位");
            row5.CreateCell(4).SetCellValue("数量");
            row5.CreateCell(5).SetCellValue("单价(元)");
            row5.CreateCell(6).SetCellValue("合计(元)");
            row5.CreateCell(7).SetCellValue("备注");
            row5.CreateCell(8).SetCellValue("规格");
            row5.CreateCell(9).SetCellValue("单位");
            row5.CreateCell(10).SetCellValue("数量");
            row5.CreateCell(11).SetCellValue("单价(元)");
            row5.CreateCell(12).SetCellValue("合计(元)");
            row5.CreateCell(13).SetCellValue("备注");
            row5.CreateCell(14).SetCellValue("经办人");
            row5.CreateCell(15).SetCellValue("供应商");
            row5.CreateCell(16).SetCellValue("票据类型");
            //ICell cell50 = row5.CreateCell(0);
            //ICell cell51 = row5.CreateCell(1);
            //ICell cell52 = row5.CreateCell(2);
            //ICell cell53 = row5.CreateCell(3);
            //ICell cell54 = row5.CreateCell(4);
            //ICell cell55 = row5.CreateCell(5);
            //ICell cell56 = row5.CreateCell(6);
            //ICell cell57 = row5.CreateCell(7);
            //ICell cell58 = row5.CreateCell(8);
            //ICell cell59 = row5.CreateCell(9);
            //ICell cell510 = row5.CreateCell(10);
            //ICell cell511 = row5.CreateCell(11);
            //ICell cell512 = row5.CreateCell(12);
            //ICell cell513 = row5.CreateCell(13);
            //ICell cell514 = row5.CreateCell(14);
            //ICell cell515 = row5.CreateCell(15);
            //ICell cell516 = row5.CreateCell(16);
            //cell50.SetCellValue("序号");
            //cell51.SetCellValue("费用名称");
            //cell52.SetCellValue("规格");
            //cell53.SetCellValue("单位");
            //cell54.SetCellValue("数量");
            //cell55.SetCellValue("单价(元)");
            //cell56.SetCellValue("合计(元)");
            //cell57.SetCellValue("备注");
            //cell58.SetCellValue("规格");
            //cell59.SetCellValue("单位");
            //cell510.SetCellValue("数量");
            //cell511.SetCellValue("单价(元)");
            //cell512.SetCellValue("合计(元)");
            //cell513.SetCellValue("备注");
            //cell514.SetCellValue("经办人");
            //cell515.SetCellValue("供应商");
            //cell516.SetCellValue("票据类型");
           
            IRow row6 = sheet.CreateRow(6);
            ICell cell60 = row6.CreateCell(0);            
            cell60.SetCellValue("1");           
            row6.CreateCell(8).SetCellValue("开发成本");
            row6.CreateCell(10).SetCellValue("1");
            row6.CreateCell(11).SetCellValue("xxxxx");//单价
            row6.CreateCell(12).SetCellValue("xxxxx");//合计
            row6.CreateCell(13).SetCellValue("xxxxx");//备注
            row6.CreateCell(14).SetCellValue("xxxxx");//经办人
            row6.CreateCell(15).SetCellValue("xxxxx");//供应商
            row6.CreateCell(16).SetCellValue("xxxxx");//票据类型
            IRow row7= sheet.CreateRow(7);
            ICell cell70 = row7.CreateCell(0);
            cell70.SetCellValue("2");
           
            IRow row8 = sheet.CreateRow(8);
            ICell cell80 = row8.CreateCell(0);
            cell80.SetCellValue("3");
            sheet.AddMergedRegion(new CellRangeAddress(6, 8, 1, 1));//费用名称xxxxx
            ICell cell61 = row6.CreateCell(1);
            cell61.SetCellValue("xxxxx");
            sheet.AddMergedRegion(new CellRangeAddress(6, 8, 2, 2));//规格xxxxx      
            ICell cell62 = row6.CreateCell(2);
            cell62.SetCellValue("xxxxx");
            sheet.AddMergedRegion(new CellRangeAddress(6, 8, 3, 3));//单位xxxxx      
            ICell cell63 = row6.CreateCell(3);
            cell63.SetCellValue("项");
            sheet.AddMergedRegion(new CellRangeAddress(6, 8, 4, 4));//数量xxxxx      
            ICell cell64 = row6.CreateCell(4);
            cell64.SetCellValue("1");
            sheet.AddMergedRegion(new CellRangeAddress(6, 8, 5, 5));//单价xxxxx      
            ICell cell65 = row6.CreateCell(5);
            cell65.SetCellValue("xxxxx");
            sheet.AddMergedRegion(new CellRangeAddress(6, 8, 6, 6));//合计xxxxx      
            ICell cell66 = row6.CreateCell(6);
            cell66.SetCellValue("xxxxx");
            sheet.AddMergedRegion(new CellRangeAddress(6, 8, 7, 7));//备注xxxxx  
            ICell cell67 = row6.CreateCell(7);
            cell67.SetCellValue("xxxxx");
           
            sheet.AddMergedRegion(new CellRangeAddress(6, 8, 9, 9));//单位xxxxx      
            ICell cell69 = row6.CreateCell(9);
            cell69.SetCellValue("项");
            /// <summary>
            /// 打开一个xls文件,把wk实例写入文件
            /// </summary>
            /// <returns></returns>  
            FileStream file = new FileStream(@"e:/test.xls", FileMode.Create);
            wk.Write(file);
            file.Close();

            //using (FileStream fs = File.OpenWrite(@"e:/myxls.xls")) //打开一个xls文件，如果没有则自行创建，如果存在myxls.xls文件则在创建时不要打开该文件！
            //{
            //    wk.Write(fs);   //向打开的这个xls文件中写入Sheet1表并保存。

            //}

        }

        ////创建工作薄
        // HSSFWorkbook wk = new HSSFWorkbook();
        // //创建一个名称为Sheet1的表     
        // ISheet sheet = wk.CreateSheet("Sheet1");
        // /// <summary>
        // /// 单元格合并
        // /// </summary>
        // /// <returns></returns>    
        // IRow row = sheet.CreateRow(0);
        // ICell cell = row.CreateCell(0);
        // cell.SetCellValue("桔子科技项目预算会签表");
        // sheet.SetColumnWidth(0, 30 * 256);
        // sheet.SetColumnWidth(1, 30 * 256);
        // ICellStyle style = wk.CreateCellStyle();
        // style.Alignment = HorizontalAlignment.Center;//居中 style6.VerticalAlignment = HSSFCellStyle.VERTICAL_CENTER;//默认居中
        // IFont font = wk.CreateFont();
        // font.FontHeight = 20 * 20;//采用 20 号字体
        // //style.BorderBottom = ICellStyle.BORDER_THIN;
        // //style.BorderLeft = ICellStyle.BORDER_THIN;
        // //style.BorderRight = ICellStyle.BORDER_THIN;
        // //style.BorderTop = ICellStyle.BORDER_THIN; 
        // //style.BottomBorderColor =IColor.GREEN.index;
        // style.SetFont(font);
        // cell.CellStyle = style;
        // sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, 11));// 把 A1:B1 这 2 个单元格合并
        // sheet.AddMergedRegion(new CellRangeAddress(12, 15, 3, 8)); //把 D13:I16 合并为一个单元格

        // IRow row7 = sheet.CreateRow(1);
        // ICell cell7 = row7.CreateCell(0);
        // ICellStyle style7 = wk.CreateCellStyle();
        // cell7.SetCellValue("项目信息");
        // style7.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.SkyBlue.Index;
        // style7.FillPattern = FillPattern.ThickForwardDiagonals;
        // style7.FillBackgroundColor = NPOI.HSSF.Util.HSSFColor.SeaGreen.Index;
        // style7.Alignment = HorizontalAlignment.Center;//居中
        // IFont font7 = wk.CreateFont();
        // font7.FontHeight = 15 * 15;//采用 20 号字体
        // style7.SetFont(font7);
        // cell7.CellStyle = style7;
        // sheet.AddMergedRegion(new CellRangeAddress(1, 1, 0, 1));// 把 A2:B2 这 2 个单元格合并
        // sheet.CreateRow(2).CreateCell(0).SetCellValue("项目名称");
        // sheet.CreateRow(3).CreateCell(0).SetCellValue("项目类别");
        // sheet.CreateRow(4).CreateCell(0).SetCellValue("项目起始日期");
        // sheet.CreateRow(5).CreateCell(0).SetCellValue("预计完成日期");
        // sheet.CreateRow(6).CreateCell(0).SetCellValue("合作方付款单位");
        // sheet.CreateRow(7).CreateCell(0).SetCellValue("合作方部门");
        // sheet.CreateRow(8).CreateCell(0).SetCellValue("合作方负责人");
        // sheet.CreateRow(9).CreateCell(0).SetCellValue("项目经办人");
        // sheet.CreateRow(10).CreateCell(0).SetCellValue("催款负责人");

        // /// <summary>
        // /// 设置页眉与页脚
        // /// </summary>
        // /// <returns></returns>              
        // sheet.Header.Center = "This is a test sheet";//设置页眉            
        // sheet.Footer.Left = "Copyright NPOI Team";//设置左页脚
        // sheet.Footer.Right = "created by "; //设置右页脚

        // /// <summary>
        // /// 日期/¥/%格式
        // /// </summary>
        // /// <returns></returns> 

        // //创建一个名称为Sheet1的表     
        // ISheet sh = wk.CreateSheet("格式");
        // ICell cell3 = sh.CreateRow(2).CreateCell(2);
        // cell3.SetCellValue(new DateTime(2016, 5, 5));
        // //set date format
        // ICellStyle cellStyle = wk.CreateCellStyle();
        // IDataFormat format = wk.CreateDataFormat();
        // cellStyle.DataFormat = format.GetFormat("yyyy 年 m 月 d 日");
        // cell3.CellStyle = cellStyle;
        // sh.SetColumnWidth(2, 20 * 256);
        // ICell cell4 = sh.CreateRow(9).CreateCell(9);
        // cell4.SetCellValue(20000);
        // //set date format
        // ICellStyle cellStyle4 = wk.CreateCellStyle();
        // IDataFormat format4 = wk.CreateDataFormat();
        // cellStyle4.DataFormat = format4.GetFormat("¥#,##0");
        // cell4.CellStyle = cellStyle4;
        // sh.SetColumnWidth(2, 20 * 256);
        // ICell cell5 = sh.CreateRow(12).CreateCell(12);
        // cell5.SetCellValue(0.9933);
        // ICellStyle cellStyle5 = wk.CreateCellStyle();
        // cellStyle5.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00%");
        // cell5.CellStyle = cellStyle5;


        // /// <summary>
        // /// 创建一个名称为mySheet的表  
        // /// </summary>
        // /// <returns></returns>     
        // ISheet tb = wk.CreateSheet("mySheet");
        // //创建一行，此行为第二行
        // IRow row1 = tb.CreateRow(1);
        // for (int i = 0; i < 10; i++)
        // {
        //     ICell cell1 = row1.CreateCell(i);  //在第二行中创建单元格
        //     cell1.SetCellValue(i);//循环往第二行的单元格中添加数据
        // }
        // ICell celSum1 = row1.CreateCell(13);
        // ICell celSum2 = row1.CreateCell(14);
        // ICell celSum3 = row1.CreateCell(15);
        // celSum1.SetCellFormula("sum(A2:E2)"); //A2+B2+C2+D2+E2
        // celSum2.SetCellFormula( "sum(A2,E2)"); //A2+E2
        // celSum3.SetCellFormula("D2*B2");
        // HSSFPatriarch patr2 = (HSSFPatriarch)tb.CreateDrawingPatriarch();
        // HSSFComment comment2 = patr2.CreateComment(new HSSFClientAnchor(0, 0, 0, 0, 11, 12, 14, 14));//让注释显示在 B3 和 E5 之间，
        // comment2.String = new HSSFRichTextString("A2+B2+C2+D2+E2");//设置这个批注的内容
        // comment2.Author = "huang";//设置这个批注的作者
        // celSum1.CellComment = comment2;
        // /// <summary>
        // ///创建 DocumentSummaryInformation 和 SummaryInformation 头
        // /// </summary>
        // /// <returns></returns>   
        // DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
        // dsi.Company = "NPOI Team公司";//公司
        // SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
        // si.Subject = "Example 主题";//主题
        // wk.DocumentSummaryInformation = dsi;
        // wk.SummaryInformation = si;

        // /// <summary>
        // ///创建 批注
        // /// </summary>
        // /// <returns></returns> 
        // ///    //创建一个名称为Sheet1的表     
        // ISheet sh1 = wk.CreateSheet("批注");
        // HSSFPatriarch patr = (HSSFPatriarch)sh1.CreateDrawingPatriarch();
        // HSSFComment comment1 = patr.CreateComment(new HSSFClientAnchor(0, 0, 0, 0, 1, 2, 4, 4));//让注释显示在 B3 和 E5 之间，
        // comment1.String = new HSSFRichTextString("Hello World");//设置这个批注的内容
        // comment1.Author = "huang";//设置这个批注的作者
        // ICell cell2 = sh1.CreateRow(1).CreateCell(1);   //批注赋给某个单元格   
        // cell2.CellComment = comment1;
        // ///    //创建一个名称为Sheet1的表     
        // ISheet sh2 = wk.CreateSheet("斜线");
        // // 创建行 
        // IRow row6 = sh2.CreateRow(0);
        // // 创建单元格 
        // ICell cell6 = row6.CreateCell(0);
        // // 画斜线 
        // HSSFPatriarch patriarch = (NPOI.HSSF.UserModel.HSSFPatriarch)sh2.CreateDrawingPatriarch();
        // HSSFClientAnchor a1 = new HSSFClientAnchor(0, 0, 0, 0, 0, 0, 1, 1);
        // HSSFSimpleShape line1 = patriarch.CreateSimpleShape(a1);
        // cell6.SetCellValue("            網絡設置" + " \n" + "圖形設置");
        // sh2.SetColumnWidth(0, 20 * 256);

        // line1.ShapeType = HSSFSimpleShape.OBJECT_TYPE_LINE;
        // line1.LineStyle = HSSFShape.LINESTYLE_SOLID;
        // // 样式设置,在这里的样式设置主要是实现换行(即上面的” \n”)   
        // HSSFCellStyle style6 = (NPOI.HSSF.UserModel.HSSFCellStyle)wk.CreateCellStyle();
        // // 允許換行(實現此效果必須設置的屬性)
        // style6.WrapText = true;
        // cell6.CellStyle = style6;
        // // 若是高度不夠,可能效果不太好    
        // row6.Height = 25 * 20;

    }
}
